from datetime import datetime, date
from typing import List

from dao.db import get_session
from sqlalchemy import select, desc
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, VARCHAR, DECIMAL, DateTime, Double
from sqlalchemy.orm import declarative_base


Base = declarative_base()


class StockRealData(Base):
    __tablename__ ='stock_real_data'
    # 定义自增主键id字段
    id = Column(Integer, primary_key=True, autoincrement=True, comment='主键id')
    ts_code = Column(VARCHAR(255), nullable=False, comment='股票代码')
    datetime = Column(DateTime, nullable=False, comment='交易日期')
    open = Column(Double, nullable=False, comment='开盘价')
    pre_close = Column(Double, nullable=False, comment='昨收价')
    price = Column(Double, nullable=False, comment='现价')
    high = Column(Double, nullable=False, comment='今日最高价')
    low = Column(Double, nullable=False, comment='今日最低价')
    increase = Column(Double, nullable=False, comment='相较于上一次的增长值')
    ratio = Column(Double, nullable=False, comment='相较于上一次的增长比例')
    volume = Column(Integer, nullable=False, comment='成交量（src=sina时是股，src=dc时是手）')
    amount = Column(Double, nullable=False, comment='成交金额（元 CNY）')

    def __repr__(self):
        return f"StockRealData(id={self.id}, ts_code='{self.ts_code}', datetime='{self.datetime}', open={self.open}, " \
               f"pre_close={self.pre_close}, price={self.price}, high={self.high}, low={self.low}, " \
               f"increase={self.increase}, ratio={self.ratio}, volume={self.volume}, amount={self.amount})"

# 根据股票代码,返回这只股票实时行情最后的一条记录
def get_last_record(target_ts_code:str):
    session = get_session()
    stmt = select(StockRealData).where(
        StockRealData.ts_code == target_ts_code,
    ).order_by(desc(StockRealData.datetime))

    # 执行查询并获取结果
    results = session.execute(stmt).scalars().all()
    session.close()
    # 返回第一条数据,也即该只股票最新的交易记录
    if results:
        return results[0]
    return None  # 如果结果为空，返回None

# 将多条记录批量插入数据库
def add_real_list(real_list: List[StockRealData]):
    session = get_session()
    session.add_all(real_list)
    session.commit()
    session.close()


def get_real_list_dao(target_ts_code:str,target_date:date):
    session = get_session()
    # 将date类型的目标日期转换为datetime类型，设置时间部分为当天的起始时间（0点0分0秒）
    start_datetime = datetime.combine(target_date, datetime.min.time())
    # 设置结束时间为当天的结束时间（23点59分59秒），以便获取当天的所有记录
    end_datetime = datetime.combine(target_date, datetime.max.time())
    stmt = select(StockRealData).where(
        StockRealData.ts_code == target_ts_code,
        StockRealData.datetime >= start_datetime,
        StockRealData.datetime <= end_datetime
    )
    results = session.execute(stmt).scalars().all()
    session.close()
    # print(results)
    return results

# get_last_record('000543.SZ')

# results = get_real_list("600519.SH",date.today())
# for row in results:
#     print(row)